﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using System.Diagnostics;

namespace OPENXMLLib
{
    /// <summary>
    /// 共享公式处理程序
    /// </summary>
    public class SharedFormulaResolver2
    {
        public static void Resolve(OpenXmlDoc doc, string sheetName)
        {
            IEnumerable<Sheet> sheets = doc.ExcelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName).ToList();
            if (sheets.Count() == 0)
            {
                //找出合适前提的sheet,没有则返回
                throw new Exception("未发现Sheet");
            }
            Sheet sheet = sheets.First();
            WorksheetPart worksheetPart = (WorksheetPart)doc.ExcelDoc.WorkbookPart.GetPartById(sheets.First().Id);
           
            SheetData data = worksheetPart.Worksheet.Elements<SheetData>().ToList().FirstOrDefault();

            List<Row> rows = data.Elements<Row>().ToList();

            List<SharedFormulaSource> sharedGroupFormula = new List<SharedFormulaSource>();


            /*
             * 第一遍处理公式单元格
                <c r="AE15" s="247">
                    <f t="shared" ref="AE15:AH25" si="0">SUMIFS(AE:AE,$N:$N,INDEX($O:$O,ROW()),$J:$J,INDEX($J:$J,ROW()))</f>
                    <v>0</v>
                </c>
             * 直接复制给ref的区域
             * 如果si有定义，那么将si和公式保存起来，给其他公式单元格使用
             * */
            #region 第一遍处理
            foreach (Row row in rows)
            {
                List<Cell> cells = row.Elements<Cell>().ToList();
                foreach (var child in cells)
                {
                    if (child.CellFormula != null
                        && (child.CellFormula.FormulaType != null && child.CellFormula.FormulaType == CellFormulaValues.Shared)
                        && !string.IsNullOrEmpty(child.CellFormula.Text) 
                        && child.CellFormula.Reference != null)
                    {
                        SharedFormulaSource sharedFormulaSource = new SharedFormulaSource(child);
                        if (child.CellFormula.SharedIndex != null && !string.IsNullOrEmpty(child.CellFormula.Text))
                        {
                            sharedGroupFormula.Add(sharedFormulaSource);
                        }

                        StringValue value = (child.CellFormula).Reference;
                        string[] values = value.Value.Split(':');
                        if (values.Count() == 2)
                        {
                            uint curRowIndex = CellReferenceUtil.GetCellRowIndex(values[0]);
                            uint curCellIndex = CellReferenceUtil.GetCellColumnIndex(values[0]);
                            string curCellColumn = CellReferenceUtil.GetCellColumnName(values[0]);
                            uint updateRowIndex = CellReferenceUtil.GetCellRowIndex(values[1]);
                            uint updateCellIndex = CellReferenceUtil.GetCellColumnIndex(values[1]);

                            //横向替换
                            if (curRowIndex == updateRowIndex)
                            {
                                for (uint index = curCellIndex + 1; index <= updateCellIndex; index++)
                                {
                                    string newCellColumn = CellReferenceUtil.ToAA((int)index);
                                    Cell updateCell = data.GetRow(updateRowIndex, false).GetCell(newCellColumn);
                                    updateCell.CellFormula = new CellFormula(child.CellFormula.InnerText.Replace(string.Format("{0}:{0}", curCellColumn), string.Format("{0}:{0}", newCellColumn)));
                                    updateCell.CellValue = new CellValue(child.CellValue.Text);
                                    updateCell.DataType = child.DataType;
                                    Debug.WriteLine(string.Format("T1-处理了单元格{0}", updateCell.CellReference.Value));
                                    Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                }
                            }
                            //纵向替换
                            else if (curCellIndex == updateCellIndex)
                            {
                                for (uint index = curRowIndex + 1; index <= updateRowIndex; index++)
                                {
                                    Cell updateCell = data.GetRow(index, false).GetCell(curCellColumn, false);
                                    if (updateCell != null)
                                    {
                                        updateCell.CellFormula = new CellFormula(child.CellFormula.InnerText);
                                        updateCell.CellValue = new CellValue(child.CellValue.Text);
                                        updateCell.DataType = child.DataType;
                                        Debug.WriteLine(string.Format("T1-处理了单元格{0}", updateCell.CellReference.Value));
                                        Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                    }
                                }
                            }
                            else
                            {
                                // 只替换一个
                                string newCellColumn = CellReferenceUtil.ToAA((int)updateCellIndex);
                                Cell updateCell = data.GetRow(updateRowIndex, false).GetCell(newCellColumn, false);
                                if (updateCell.CellFormula != null && updateCell.CellFormula.Reference != null)
                                {
                                    updateCell.CellFormula = new CellFormula(child.CellFormula.InnerText);
                                }
                                else
                                {
                                    updateCell.CellFormula = new CellFormula(child.CellFormula.InnerText);
                                    updateCell.CellFormula.Reference = null;
                                    updateCell.CellFormula.FormulaType = null;
                                    updateCell.CellFormula.SharedIndex = null;
                                    updateCell.CellValue = new CellValue(child.CellValue.Text);
                                    updateCell.DataType = child.DataType;
                                }
                                Debug.WriteLine(string.Format("T1-处理了单元格{0}", updateCell.CellReference.Value));
                                Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                            }

                            //去除Reference
                            (child.CellFormula).Reference = null;
                            (child.CellFormula).FormulaType = null;
                            (child.CellFormula).SharedIndex = null;
                        }
                        else
                        {
                            System.Diagnostics.Debug.WriteLine(value.Value);
                        }
                    }
                }
            }
            #endregion

            #region 第二遍处理
            /*
            <c r="AF15" s="247">
                <f t="shared" si="0"/>
                <v>0</v>
            </c>
             * */
            foreach (Row row in rows)
            {
                List<Cell> cells = row.Elements<Cell>().ToList();
                foreach (var child in cells)
                {
                    if (child.CellFormula != null && child.CellFormula.FormulaType != null && child.CellFormula.FormulaType == CellFormulaValues.Shared)
                    {
                        uint sharedIndex = child.CellFormula.SharedIndex.Value;

                        SharedFormulaSource foundFormula = null;
                        foreach (var sharedFormula in sharedGroupFormula)
                        {
                            if (sharedFormula.SharedIndex == sharedIndex)
                            {
                                foundFormula = sharedFormula;
                                break;
                            }
                        }

                        if (foundFormula != null)
                        {
                            child.CellFormula = new CellFormula(foundFormula.SourceFormula);
                            child.CellFormula.Reference = null;
                            child.CellFormula.FormulaType = null;
                            child.CellFormula.SharedIndex = null;
                            child.CellValue = new CellValue(foundFormula.SourceCellValue);
                            if (foundFormula.HasError)
                            {
                                child.DataType = CellValues.Error;
                            }
                            else
                            {
                                child.DataType = null;
                            }
                            Debug.WriteLine(string.Format("T2-处理了单元格{0}", child.CellReference.Value));
                            Debug.WriteLine(string.Format("    f:{0}", foundFormula.SourceFormula));
                        }
                    }
                }
            }
            #endregion

            worksheetPart.Worksheet.Save();
        }
        public static void Resolve(SpreadsheetDocument doc)
        {
            IEnumerable<Sheet> sheets = doc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().ToList();
            if (sheets.Count() == 0)
            {
                //找出合适前提的sheet,没有则返回
                throw new Exception("未发现Sheet");
            }
            foreach (var sheet in sheets)
            {
                WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);

                SheetData data = worksheetPart.Worksheet.Elements<SheetData>().ToList().FirstOrDefault();

                List<Row> rows = data.Elements<Row>().ToList();

                List<SharedFormulaSource> sharedGroupFormula = new List<SharedFormulaSource>();


                /*
                 * 第一遍处理公式单元格
                    <c r="AE15" s="247">
                        <f t="shared" ref="AE15:AH25" si="0">SUMIFS(AE:AE,$N:$N,INDEX($O:$O,ROW()),$J:$J,INDEX($J:$J,ROW()))</f>
                        <v>0</v>
                    </c>
                 * 直接复制给ref的区域
                 * 如果si有定义，那么将si和公式保存起来，给其他公式单元格使用
                 * */
                #region 第一遍处理
                foreach (Row row in rows)
                {
                    List<Cell> cells = row.Elements<Cell>().ToList();
                    foreach (var child in cells)
                    {
                        if (child.CellFormula != null
                            && (child.CellFormula.FormulaType != null && child.CellFormula.FormulaType == CellFormulaValues.Shared)
                            && !string.IsNullOrEmpty(child.CellFormula.Text)
                            && child.CellFormula.Reference != null)
                        {
                            SharedFormulaSource sharedFormulaSource = new SharedFormulaSource(child);
                            if (child.CellFormula.SharedIndex != null && !string.IsNullOrEmpty(child.CellFormula.Text))
                            {
                                sharedGroupFormula.Add(sharedFormulaSource);
                            }

                            StringValue value = (child.CellFormula).Reference;
                            string[] values = value.Value.Split(':');
                            if (values.Count() == 2)
                            {
                                uint curRowIndex = CellReferenceUtil.GetCellRowIndex(values[0]);
                                uint curCellIndex = CellReferenceUtil.GetCellColumnIndex(values[0]);
                                string curCellColumn = CellReferenceUtil.GetCellColumnName(values[0]);
                                uint updateRowIndex = CellReferenceUtil.GetCellRowIndex(values[1]);
                                uint updateCellIndex = CellReferenceUtil.GetCellColumnIndex(values[1]);

                                //横向替换
                                if (curRowIndex == updateRowIndex)
                                {
                                    for (uint index = curCellIndex + 1; index <= updateCellIndex; index++)
                                    {
                                        string newCellColumn = CellReferenceUtil.ToAA((int)index);
                                        Cell updateCell = data.GetRow(updateRowIndex, false).GetCell(newCellColumn);
                                        if (updateCell != null)
                                        {
                                            if (updateCell.CellFormula != null)
                                            {
                                                if (updateCell.CellFormula.FormulaType !=null && updateCell.CellFormula.FormulaType == CellFormulaValues.Shared)
                                                {
                                                    updateCell.CellFormula = new CellFormula(child.CellFormula.InnerText.Replace(string.Format("{0}:{0}", curCellColumn), string.Format("{0}:{0}", newCellColumn)));
                                                    updateCell.CellValue = new CellValue(child.CellValue.Text);
                                                    updateCell.DataType = child.DataType;
                                                    Debug.Write(string.Format("横向处理了单元格{0}", updateCell.CellReference.Value));
                                                    Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                                }
                                                else
                                                {
                                                    Debug.Write(string.Format("[失败-目标单元格不是共享公式单元格]纵向处理了单元格:{0}{1}", newCellColumn, updateRowIndex));
                                                    Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                                }
                                            }
                                            else
                                            {
                                                Debug.Write(string.Format("[失败-目标单元格不是公式单元格]纵向处理了单元格:{0}{1}", newCellColumn, updateRowIndex));
                                                Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                            }
                                        }
                                        else
                                        {
                                            Debug.Write(string.Format("[失败]纵向处理了单元格:{0}{1}", newCellColumn, updateRowIndex));
                                            Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                        }
                                    }
                                }
                                //纵向替换
                                else if (curCellIndex == updateCellIndex)
                                {
                                    for (uint index = curRowIndex + 1; index <= updateRowIndex; index++)
                                    {
                                        Cell updateCell = data.GetRow(index, false).GetCell(curCellColumn, false);
                                        if (updateCell != null)
                                        {
                                            if (updateCell.CellFormula != null)
                                            {
                                                if (updateCell.CellFormula.FormulaType != null && updateCell.CellFormula.FormulaType == CellFormulaValues.Shared)
                                                {
                                                    updateCell.CellFormula = new CellFormula(child.CellFormula.InnerText);
                                                    updateCell.CellValue = new CellValue(child.CellValue.Text);
                                                    updateCell.DataType = child.DataType;
                                                    Debug.Write(string.Format("纵向处理了单元格{0}", updateCell.CellReference.Value));
                                                    Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                                }
                                                else
                                                {
                                                    Debug.Write(string.Format("[失败-目标单元格不是共享公式单元格]纵向处理了单元格:{0}{1}", curCellColumn, index));
                                                    Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                                }
                                            }
                                            else
                                            {
                                                Debug.Write(string.Format("[失败-目标单元格不是公式单元格]纵向处理了单元格:{0}{1}", curCellColumn, index));
                                                Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                            }
                                        }
                                        else
                                        {
                                            Debug.Write(string.Format("[失败]纵向处理了单元格:{0}{1}", curCellColumn, index));
                                            Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                        }
                                    }
                                }
                                else
                                {
                                    // 只替换一个
                                    string newCellColumn = CellReferenceUtil.ToAA((int)updateCellIndex);
                                    Cell updateCell = data.GetRow(updateRowIndex, false).GetCell(newCellColumn, false);
                                    if (updateCell != null)
                                    {
                                        if (updateCell.CellFormula != null)
                                        {
                                            if (updateCell.CellFormula.FormulaType != null && updateCell.CellFormula.FormulaType == CellFormulaValues.Shared)
                                            {
                                                updateCell.CellFormula = new CellFormula(child.CellFormula.InnerText);
                                                updateCell.CellFormula.Reference = null;
                                                updateCell.CellFormula.FormulaType = null;
                                                updateCell.CellFormula.SharedIndex = null;
                                                updateCell.CellValue = new CellValue(child.CellValue.Text);
                                                updateCell.DataType = child.DataType;
                                                Debug.Write(string.Format("单个处理了单元格{0}", updateCell.CellReference.Value));
                                                Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                            }
                                            else
                                            {
                                                Debug.Write(string.Format("[失败-目标单元格不是共享公式单元格]纵向处理了单元格:{0}", updateCell.CellReference.Value));
                                                Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                            }
                                        }
                                        else
                                        {
                                            Debug.Write(string.Format("[失败-目标单元格不是公式单元格]纵向处理了单元格:{0}", updateCell.CellReference.Value));
                                            Debug.WriteLine(string.Format("    f:{0}", child.CellFormula.InnerText));
                                        }
                                    }
                                    else
                                    {
                                        Debug.WriteLine(string.Format("错误,单个处理了单元格,但是未发现单元格{0}{1}", newCellColumn, updateRowIndex));
                                    }
                                }

                                //去除Reference
                                (child.CellFormula).Reference = null;
                                (child.CellFormula).FormulaType = null;
                                (child.CellFormula).SharedIndex = null;
                            }
                            else
                            {
                                System.Diagnostics.Debug.WriteLine(value.Value);
                            }
                        }
                    }
                }
                #endregion

                #region 第二遍处理
                /*
            <c r="AF15" s="247">
                <f t="shared" si="0"/>
                <v>0</v>
            </c>
             * */
                foreach (Row row in rows)
                {
                    List<Cell> cells = row.Elements<Cell>().ToList();
                    foreach (var child in cells)
                    {
                        if (child.CellFormula != null && child.CellFormula.FormulaType != null && child.CellFormula.FormulaType == CellFormulaValues.Shared)
                        {
                            uint sharedIndex = child.CellFormula.SharedIndex.Value;

                            SharedFormulaSource foundFormula = null;
                            foreach (var sharedFormula in sharedGroupFormula)
                            {
                                if (sharedFormula.SharedIndex == sharedIndex)
                                {
                                    foundFormula = sharedFormula;
                                    break;
                                }
                            }

                            if (foundFormula != null)
                            {
                                child.CellFormula = new CellFormula(foundFormula.SourceFormula);
                                child.CellFormula.Reference = null;
                                child.CellFormula.FormulaType = null;
                                child.CellFormula.SharedIndex = null;
                                child.CellValue = new CellValue(foundFormula.SourceCellValue);
                                if (foundFormula.HasError)
                                {
                                    child.DataType = CellValues.Error;
                                }
                                else
                                {
                                    child.DataType = null;
                                }
                                Debug.Write(string.Format("T2-处理了单元格{0}", child.CellReference.Value));
                                Debug.WriteLine(string.Format("    f:{0}", foundFormula.SourceFormula));
                            }
                        }
                    }
                }
                #endregion

                worksheetPart.Worksheet.Save();
            }
        }

        /// <summary>
        /// 处理文档中的公式，将公式单元格的信息都删除，然后重新添加
        /// </summary>
        /// <param name="doc"></param>
        public static void ResolveCalcChain(OpenXmlDoc doc)
        {
            CalculationChainPart calculationChainPart = doc.ExcelDoc.WorkbookPart.CalculationChainPart;
            CalculationChain calcChain = calculationChainPart.CalculationChain;
            Debug.WriteLine("清除现有公式链");
            calcChain.RemoveAllChildren();

            List<string> sheetNames = doc.GetSheets();
            foreach (var sheetName in sheetNames)
            {
                IEnumerable<Sheet> sheets = doc.ExcelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName).ToList();
                if (sheets.Count() == 0)
                {
                    continue;
                }
                Sheet sheet = sheets.First();
                WorksheetPart worksheetPart = (WorksheetPart)doc.ExcelDoc.WorkbookPart.GetPartById(sheets.First().Id);
                SheetData data = worksheetPart.Worksheet.Elements<SheetData>().ToList().FirstOrDefault();
                List<Row> rows = data.Elements<Row>().ToList();
                bool isFirst = true;
                foreach (Row row in rows)
                {
                    List<Cell> cells = row.Elements<Cell>().ToList();
                    foreach (Cell cell in cells)
                    {
                        if (cell.CellFormula != null)
                        {
                            CalculationCell calcCell = new CalculationCell();
                            calcCell.CellReference = new StringValue(cell.CellReference.Value);
                            if (isFirst)
                            {
                                Debug.WriteLine("附加{0}中的公式到公式链", sheet.Name);
                                calcCell.SheetId = (int)sheet.SheetId.Value;
                                isFirst = false;
                            }
                            calcChain.AppendChild<CalculationCell>(calcCell);
                        }
                    }
                }
            }

            calcChain.Save();
            //calcChain.RemoveAllChildren<CalculationCell>();
        }

        public static void ResolveCalcChain(SpreadsheetDocument doc)
        {
            CalculationChainPart calculationChainPart = doc.WorkbookPart.CalculationChainPart;
            CalculationChain calcChain = calculationChainPart.CalculationChain;
            calcChain.RemoveAllChildren();

            IList<Sheet> sheets = doc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().ToList();
            if (sheets.Count() == 0)
            {
                return;
            }
            foreach (var sheet in sheets)
            {
                WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);
                SheetData data = worksheetPart.Worksheet.Elements<SheetData>().ToList().FirstOrDefault();
                List<Row> rows = data.Elements<Row>().ToList();
                bool isFirst = true;
                foreach (Row row in rows)
                {
                    List<Cell> cells = row.Elements<Cell>().ToList();
                    foreach (Cell cell in cells)
                    {
                        if (cell.CellFormula != null)
                        {
                            CalculationCell calcCell = new CalculationCell();
                            calcCell.CellReference = new StringValue(cell.CellReference.Value);
                            if (isFirst)
                            {
                                calcCell.SheetId = (int)sheet.SheetId.Value;
                                isFirst = false;
                            }
                            calcChain.AppendChild<CalculationCell>(calcCell);
                        }
                    }
                }
            }

            calcChain.Save();
            //calcChain.RemoveAllChildren<CalculationCell>();
        }

        public static void AdjustCalculationChain(SpreadsheetDocument document, WorksheetPart worksheetPart, Sheet sheet, SheetData sheetData)
        {
            CalculationChainPart calculationChainPart = document.WorkbookPart.CalculationChainPart;
            CalculationChain calculationChain = calculationChainPart.CalculationChain;
            //List<CalculationCell> calculationCells = calculationChain.Elements<CalculationCell>().Where(c => c.SheetId != null && c.SheetId.Value == sheet.SheetId.Value).ToList();
            List<CalculationCell> calculationCells = new List<CalculationCell>();
            bool IsExist = false;
            foreach (CalculationCell c in calculationChain.Elements<CalculationCell>().ToList())
            {
                if (c.SheetId != null && c.SheetId.Value == sheet.SheetId.Value)
                {
                    IsExist = true;
                }
                else if (c.SheetId != null && c.SheetId.Value != sheet.SheetId.Value)
                {
                    IsExist = false;
                }
                if (IsExist)
                {
                    calculationCells.Add(c);
                }
            }

            foreach (CalculationCell cal in calculationCells)
            {
                int rowIndex = (int)CellReferenceUtil.GetCellRowIndex(cal.CellReference.Value);
                Row row = sheetData.Elements<Row>().Where(p => p.RowIndex.Value == rowIndex).SingleOrDefault();
                if (null != row)
                {
                    Cell cell = row.Elements<Cell>().Where(p => p.CellReference.Value == cal.CellReference.Value).SingleOrDefault();
                    if (cell == null)
                    {
                        cal.Remove();
                    }
                    else if ((cal.InChildChain == null || !cal.InChildChain.Value) && (cell.CellFormula == null || string.IsNullOrEmpty(cell.CellFormula.Text)))
                    {
                        cal.Remove();
                    }
                }
                else
                {
                    cal.Remove();
                }
            }

            //MergeCells mergeCells;
            //if (worksheetPart.Worksheet.Elements<MergeCells>().Count() > 0)
            //{
            //    mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First();
            //    List<string> hasReference = new List<string>();
            //    List<MergeCell> hasMergeCells = new List<MergeCell>();
            //    foreach (MergeCell mergeCell in mergeCells)
            //    {
            //        if (hasReference.Contains(mergeCell.Reference.Value))
            //        {
            //            hasMergeCells.Add(mergeCell);
            //        }
            //        else
            //        {
            //            hasReference.Add(mergeCell.Reference.Value);
            //        }
            //    }
            //    foreach (MergeCell mergeCell in hasMergeCells)
            //    {
            //        mergeCell.Remove();
            //    }
            //}
        }
    }
}
